"""db.modify

Apply manual database schema changes that SQLAlchemy's `create_all` cannot handle.

Place **idempotent** DDL statements here. They will run once on every app start
(after `Base.metadata.create_all`). Make sure each statement is safe to execute
multiple times (or wrap it in a conditional).
"""

from typing import Sequence

from sqlalchemy.ext.asyncio import AsyncEngine
from sqlalchemy import text

# 引入应用级日志工具
from log import log_info, log_error


async def apply(engine: AsyncEngine) -> None:  # noqa: D401
    """Run custom, idempotent migrations."""

    # Add your DDL statements below. Use triple-quoted strings for readability.
    ddls: Sequence[str] = [
        # 调整 compliance_template.assessment_type 字段长度
        """
        DO $$
        BEGIN
            -- 检查字段类型是否需要调整
            IF EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'compliance_template' 
                AND column_name = 'assessment_type'
                AND character_maximum_length < 20
            ) THEN
                ALTER TABLE compliance_template
                ALTER COLUMN assessment_type TYPE varchar(20)
                USING assessment_type::varchar(20);
            END IF;
        END $$;
        """,
        
        # 调整 assessment_plan.assessment_type 字段长度
        """
        DO $$
        BEGIN
            -- 检查字段类型是否需要调整
            IF EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'assessment_plan' 
                AND column_name = 'assessment_type'
                AND character_maximum_length < 20
            ) THEN
                ALTER TABLE assessment_plan
                ALTER COLUMN assessment_type TYPE varchar(20)
                USING assessment_type::varchar(20);
            END IF;
        END $$;
        """,

        # 如果 compliance_item_issue.original_description 字段不存在，则新增
        """
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'compliance_item_issue' 
                AND column_name = 'original_description'
            ) THEN
                ALTER TABLE compliance_item_issue
                ADD COLUMN original_description TEXT;
            END IF;
        END $$;
        """,

        # 修改 remediations 的 name 字段为 title（如果 name 列存在）
        """
        DO $$
        BEGIN
            IF EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'remediations' AND column_name = 'name'
            ) THEN
                ALTER TABLE remediations RENAME COLUMN name TO title;
            END IF;
        END $$;
        """,

        # 迁移 proofmapper_snapshots 表结构（支持多版本快照）
        """
        DO $$
        DECLARE
            has_old_structure BOOLEAN;
        BEGIN
            -- 检查表是否存在
            IF EXISTS (SELECT FROM information_schema.tables 
                       WHERE table_name = 'proofmapper_snapshots') THEN
                
                -- 检查是否是旧结构（name列允许为NULL，或没有正确的约束）
                SELECT EXISTS (
                    SELECT FROM information_schema.columns 
                    WHERE table_name = 'proofmapper_snapshots' 
                    AND column_name = 'name' 
                    AND is_nullable = 'YES'
                ) INTO has_old_structure;
                
                -- 如果是旧结构，才进行迁移
                IF has_old_structure THEN
                    -- 备份旧数据到临时表
                    CREATE TEMP TABLE proofmapper_snapshots_backup AS 
                    SELECT * FROM proofmapper_snapshots;
                    
                    -- 删除旧表
                    DROP TABLE proofmapper_snapshots CASCADE;
                    
                    -- 创建新表结构
                    CREATE TABLE proofmapper_snapshots (
                        id VARCHAR PRIMARY KEY,
                        assessment_type VARCHAR NOT NULL,
                        name VARCHAR NOT NULL,
                        saved_at TIMESTAMPTZ DEFAULT now(),
                        evaluation_items JSONB
                    );
                    
                    -- 创建索引（如果不存在）
                    CREATE INDEX IF NOT EXISTS idx_proofmapper_assessment_type 
                    ON proofmapper_snapshots(assessment_type);
                    
                    -- 迁移旧数据
                    INSERT INTO proofmapper_snapshots (id, assessment_type, name, saved_at, evaluation_items)
                    SELECT 
                        gen_random_uuid()::text,
                        assessment_type,
                        COALESCE(name, assessment_type || '_default'),
                        saved_at,
                        evaluation_items
                    FROM proofmapper_snapshots_backup
                    ON CONFLICT (id) DO NOTHING;
                    
                    -- 清理临时表
                    DROP TABLE IF EXISTS proofmapper_snapshots_backup;
                END IF;
            END IF;
        END $$;
        """,

        # 为 template_item 表添加 maturity_level 字段（成熟度等级）
        """
        DO $$
        BEGIN
            -- 检查 maturity_level 字段是否存在
            IF NOT EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'template_item' 
                AND column_name = 'maturity_level'
            ) THEN
                ALTER TABLE template_item
                ADD COLUMN maturity_level INTEGER DEFAULT 1;
                
                -- 添加字段注释
                COMMENT ON COLUMN template_item.maturity_level IS '成熟度等级';
            END IF;
        END $$;
        """,

        # 为 compliance_item 表添加 maturity_level 字段（成熟度等级）
        """
        DO $$
        BEGIN
            -- 检查 maturity_level 字段是否存在
            IF NOT EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'compliance_item' 
                AND column_name = 'maturity_level'
            ) THEN
                ALTER TABLE compliance_item
                ADD COLUMN maturity_level INTEGER DEFAULT 1;
                
                -- 添加字段注释
                COMMENT ON COLUMN compliance_item.maturity_level IS '成熟度等级';
            END IF;
        END $$;
        """,

        # 为 compliance_template 表添加 default_maturity_level 字段（默认成熟度等级）
        """
        DO $$
        BEGIN
            -- 检查 default_maturity_level 字段是否存在
            IF NOT EXISTS (
                SELECT FROM information_schema.columns 
                WHERE table_name = 'compliance_template' 
                AND column_name = 'default_maturity_level'
            ) THEN
                ALTER TABLE compliance_template
                ADD COLUMN default_maturity_level INTEGER NOT NULL DEFAULT 1;
                
                -- 添加字段注释
                COMMENT ON COLUMN compliance_template.default_maturity_level IS '默认成熟度等级';
            END IF;
        END $$;
        """,

        # 为 risk_level 枚举类型添加新值（very-high 和 very-low）
        """
        DO $$
        BEGIN
            -- 添加 'very-high' 枚举值（如果不存在）
            IF NOT EXISTS (
                SELECT 1 FROM pg_enum 
                WHERE enumlabel = 'very-high' 
                AND enumtypid = (
                    SELECT oid FROM pg_type WHERE typname = 'risk_level'
                )
            ) THEN
                ALTER TYPE risk_level ADD VALUE 'very-high' BEFORE 'high';
            END IF;
            
            -- 添加 'very-low' 枚举值（如果不存在）
            IF NOT EXISTS (
                SELECT 1 FROM pg_enum 
                WHERE enumlabel = 'very-low' 
                AND enumtypid = (
                    SELECT oid FROM pg_type WHERE typname = 'risk_level'
                )
            ) THEN
                ALTER TYPE risk_level ADD VALUE 'very-low' AFTER 'low';
            END IF;
        END $$;
        """
    ]

    if not ddls:
        return

    async with engine.begin() as conn:
        for idx, ddl in enumerate(ddls, start=1):
            try:
                log_info(f"Executing DDL #{idx} …")
                await conn.execute(text(ddl))
                log_info(f"DDL #{idx} executed successfully")
            except Exception as exc:
                log_error(f"DDL #{idx} failed: {exc}")
